# coding=utf8


import sqlite3


class Demo:

    @staticmethod
    def main(db='students.db'):
        Demo.create_insert(db)
        Demo.read_sql(db)
        Demo.use_placeholder(db)

    @staticmethod
    def look_database(db='students.db'):
        cnn = sqlite3.connect(db)
        cur = cnn.cursor()
        info = cur.execute("""select * from sqlite_master""")
        r = info.fetchall()
        info_tables = cur.execute("""select * from sqlite_master where type = ?""", ('table',))
        rt = info_tables.fetchall()
        # print(r, rt)
        print(
            f"# 连接数据库\n"
            f" >>> cnn = sqlite3.connect(db={db})\n"
            " >>> cur = cnn.cursor()\n"
            "# 查看数据库中存储数据的信息\n"
            ' >>> info = cur.execute("""select * from sqlite_master""")\n'
            ' >>> info.fetchall()\n'
            f"{r}\n"
            "# 查看数据库中存储数据表\n"
            ' >>> info_tables = cur.execute("""select * from sqlite_master where type = ?""", ("table",))\n'
            ' >>> info_tables.fetchall()\n'
            f"{rt}\n"
            f" >>> cur.close()\n"
            f" >>> cnn.close()"
        )
        cur.close()
        cnn.close()
        return

    @staticmethod
    def create_insert(db='students.db'):
        # (1) 创建数据库、写入数据
        print(
            "# 创建数据库、写入数据\n"
            " >>> import sqlite3\n"
            " >>> cnn = sqlite3.connect('students.db')\n"
            " >>> cur = cnn.cursor()\n"
            " >>> cur.execute('''CREATE TABLE score (id text, name text, math integer, art real)''')\n"
            " >>> cur.execute('''INSERT INTO score VALUES ('21101','LiQiang', 90, 80.5)''')\n"
            " >>> conn.commit()\n"
            " >>> cur.close()\n"
            " >>> cnn.close()"
        )
        conn = sqlite3.connect(db)
        cur = conn.cursor()
        try:
            cur.execute('''CREATE TABLE score (id text, name text, math integer, art real)''')
        except sqlite3.OperationalError:
            cur.execute('''DROP TABLE score''')
            cur.execute('''CREATE TABLE score (id text, name text, math integer, art real)''')
            print("--- drop and recreated table students!")
        cur.execute("INSERT INTO score VALUES ('21101', 'LiQiang', 90, 80.8)")
        conn.commit()
        cur.close()
        conn.close()
        return

    @staticmethod
    def read_sql(db='students.db'):
        # (2) 读取数据库数据
        conn = sqlite3.connect(db)
        cur = conn.cursor()
        r = cur.execute('''select * from score''')
        print(
            "# 连接读取数据库数据\n"
            " >>> conn = sqlite3.connect('students.db')\n"
            " >>> cur = conn.cursor()\n"
            " >>> r = cur.execute('''select * from score''')\n"
            " >>> r.fetchall()\n"
            f"{r.fetchall()}\n"
            " >>> cur.close()\n"
            " >>> comm.close()\n"
        )
        cur.close()
        conn.close()

    @staticmethod
    def use_placeholder(db='students.db'):
        # （3）使用参数替换方式
        conn = sqlite3.connect(db)
        cur = conn.cursor()
        data = [('21102', 'He', 90, 70), ('21103', 'Ma', 70, 60)]
        cur.executemany('''Insert Into score values (?, ?, ?, ?)''', data)
        cur.execute('''update score set name = ? where id = ?''', ('Su', '21101'))
        conn.commit()
        r = cur.execute('''select * from score''')
        print(
            "# 在SQL语句中使用占位符?, 进行参数替换\n"
            " >>> conn = sqlite3.connect('students.db')\n"
            " >>> cur = conn.cursor()\n"
            "# 使用executemany执行多条Insert语句，向表score插入多条记录\n"
            " >>> data = [('21102', 'He', 90, 70), ('21103', 'Ma', 70, 60)]\n"
            " >>> cur.executemany('''INSERT INTO score VALUES (?, ?, ?, ?)''', data)\n"
            "# 修改score表中id=‘21101’记录name字段\n"
            " >>> cur.execute('''UPDATE score SET name = ? WHERE id = ?''', ('Su', '21101'))\n"
            "# 提交事务保持修改\n"
            " >>> conn.commit()\n"
            "# 查询表score内容\n"
            " >>> r = cur.execute('''select * from score''')\n"
            " >>> r.fetchmany(size=2)\n"
            f"{r.fetchmany(size=2)}\n"
            " >>> r.fetchall()\n"
            f"{r.fetchall()}\n"
            "# 关闭游标和连接\n"
            " >>> cur.close()\n"
            " >>> comm.close()\n"
        )
        # conn.commit()
        cur.close()
        conn.close()

        return

    @staticmethod
    def pandas_read_sql_table(db='students.db'):
        # Demo.look_database(db)
        import pandas as pd
        cnn = sqlite3.connect(db)

        # not supported, only support sqlalchemy
        # df = pd.read_sql_table('score', con=cur, index_col='id')
        # df = pd.read_sql_table('score', con=cnn)

        print(
            " >>> cnn = sqlite3.connect(db)\n"
            " >>> df = pd.read_sql_query('select * from score', con=cnn)\n"
            " >>> print(df)"
        )
        df = pd.read_sql_query('select * from score', con=cnn)
        print(df)

        print(
            """\n >>> df1 = pd.read_sql(sql="SELECT * FROM score where id = '21102'", con=cnn)\n"""
            " >>> print(df1)"
        )
        df = pd.read_sql(sql="""SELECT * FROM score where id = '21102'""", con=cnn)
        print(df)

        cnn.close()

        return
